﻿//===========================================================
//	Copyright (C) 2012 ARFEN
//	All rights reserved
//===========================================================

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Text;
using MySql.Data.MySqlClient;

namespace eSYSU.Data.DataAccess
{
    /// <summary>
    ///SqlHelper 封装高性能，可扩展的SqlClient
    /// </summary>
    public abstract class SqlHelper
    {
        //数据库连接字符串
		public static readonly string ConnString = ConfigurationManager.ConnectionStrings["SQLServerConnString"].ConnectionString;
        //ACCESS 数据库连接字符串
        //public static readonly string ConnString = ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString+System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]);

        //数据库类型
        private static string dataProvider = ConfigurationManager.AppSettings["provider"];


        /// <summary>
        /// 执行一个SqlCommand（即返回任何结果集）对在连接字符串中指定的数据库
        /// 使用提供的参数。
        /// </summary>
        /// <param name="connectionString">一个有效的SqlConnection连接字符串</param>
        /// <param name="cmdType">CommandType枚举类型（存储过程，文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            using (IDbConnection conn = CreateConnection(connString))
            {
                using (IDbCommand cmd = CreateCommand(cmdText, conn))
                {
                    PrepareCommand(conn, cmd, cmdType, cmdText, parmsDic);
                    int val = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        /// <summary>
        /// 执行一个SqlCommand返回在连接字符串中指定的数据库结果集
        /// 使用提供的参数。
        /// </summary>
        /// <param name="connectionString">一个有效的SqlConnection连接字符串</param>
        /// <param name="cmdType">CommandType枚举类型（存储过程，文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        /// <returns>一个SqlDataReader结果</returns>
        public static IDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            IDbConnection conn = CreateConnection(connString);
            IDbCommand cmd = CreateCommand(cmdText, conn);

            try
            {
                PrepareCommand(conn, cmd, cmdType, cmdText, parmsDic);
                IDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        /// 执行查询，返回查询所返回的结果集中第一行的第一列
        /// 使用提供的参数。
        /// </summary>
        /// <param name="connectionString">SqlConnection连接字符串</param>
        /// <param name="cmdType">CommandType枚举类型（存储过程，文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        /// <returns>一个应该转换为使用Convert.To期望类型的对象</returns>
        public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            using (IDbConnection connection = CreateConnection(connString))
            {
                using (IDbCommand cmd = CreateCommand(cmdText, connection))
                {
                    PrepareCommand(connection, cmd, cmdType, cmdText, parmsDic);
                    object val = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return val;
                }
            }
        }

        /// <summary>
        /// 执行一个SqlCommand，返回数据集中的一张表
        /// </summary>
        /// <param name="connectionString">现有的数据库连接</param>
        /// <param name="cmdType">CommandType枚举类型（存储过程，文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string connString, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            DataSet ds = new DataSet();

            using (IDbConnection conn = CreateConnection(connString))
            {
                using (IDbCommand cmd = CreateCommand(cmdText, conn))
                {
                    PrepareCommand(conn, cmd, cmdType, cmdText, parmsDic);
                    IDbDataAdapter ida = CreateDataAdapter(cmd);
                    ida.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 直接执行一条SQL语句，并且返回DataTable对象
        /// </summary>
        /// <param name="procName">命令文本</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetDataTable(string sSQL)
        {
            try
            {
                IDbDataAdapter sda = CreateDataAdapter(CreateCommand(sSQL, CreateConnection(ConnString)));
                DataSet ds = new DataSet();
                sda.Fill(ds);

                //得到执行成功返回值
                return ds.Tables[0];
            }
            catch (MySqlException ex)
            {
                throw (ex);
            }
        }



        /// <summary>
        /// 执行一个SqlCommand，返回数据集
        /// </summary>
        /// <param name="connString">现有的数据库连接</param>
        /// <param name="cmdType">CommandType枚举类型（存储过程，文本等）</param>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        /// <returns>DataTable结果</returns>
        public static DataSet GetDataSet(string connString, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            DataSet ds = new DataSet();

            using (IDbConnection conn = CreateConnection(connString))
            {
                using (IDbCommand cmd = CreateCommand(cmdText, conn))
                {
                    PrepareCommand(conn, cmd, cmdType, cmdText, parmsDic);
                    IDbDataAdapter ida = CreateDataAdapter(cmd);
                    ida.Fill(ds);
                    cmd.Parameters.Clear();
                    return ds;
                }
            }
        }


        /// <summary>
        /// 准备执行命令
        /// </summary>
        /// <param name="conn">Connection对象</param>
        /// <param name="cmd">Command对象</param>
        /// <param name="cmdType">CommandType类型，例如：存储过程或文本</param>
        /// <param name="cmdText">命令文本，例如：Select * from abc</param>
        /// <param name="parmsDic">IDataParameter的集合Dictionary类型</param>
        private static void PrepareCommand(IDbConnection conn, IDbCommand cmd, CommandType cmdType, string cmdText, IDictionary<string, object> parmsDic)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (parmsDic != null)
            {
                foreach (string str in parmsDic.Keys)
                {
                    IDataParameter parm = cmd.CreateParameter();
                    parm.ParameterName = str;
                    parm.Value = parmsDic[str];
                    cmd.Parameters.Add(parm);
                }
            }
        }


        /// <summary>
        /// 创建并初始化Connection连接实例
        /// </summary>
        /// <param name="connString">Connection连接字符串</param>
        /// <returns>返回一个已经创建好的数据库连接实例</returns>
        private static IDbConnection CreateConnection(string connString)
        {
            IDbConnection conn = null;
            switch (dataProvider)
            {
                case "SqlServer":
                    conn = new SqlConnection(connString);
                    break;
                case "OleDb":
                    conn = new OleDbConnection(connString);
                    break;
                case "Odbc":
                    conn = new OdbcConnection(connString);
                    break;
                case "MySql":
                    conn = new MySqlConnection(connString);
                    break;
            }

            return conn;
        }


        /// <summary>
        /// 创建并初始化Command实例
        /// </summary>
        /// <param name="cmdText">存储过程名称或T-SQL命令</param>
        /// <param name="conn">数据库连接实例</param>
        /// <returns>返回一个已经创建好的Command实例对象</returns>
        private static IDbCommand CreateCommand(string cmdText, IDbConnection conn)
        {
            IDbCommand cmd = null;
            switch (dataProvider)
            {
                case "SqlServer":
                    cmd = new SqlCommand(cmdText, (SqlConnection)conn);
                    break;
                case "OleDb":
                    cmd = new OleDbCommand(cmdText, (OleDbConnection)conn);
                    break;
                case "Odbc":
                    cmd = new OdbcCommand(cmdText, (OdbcConnection)conn);
                    break;
                case "MySql":
                    cmd = new MySqlCommand(cmdText, (MySqlConnection)conn);
                    break;
            }

            return cmd;
        }

        /// <summary>
        /// 创建并初始化一个DataAdapter对象
        /// </summary>
        /// <param name="cmd">Command实例对象</param>
        /// <returns>返回一个已经创建好的DataAdapter对象</returns>
        private static IDbDataAdapter CreateDataAdapter(IDbCommand cmd)
        {
            IDbDataAdapter ida = null;
            switch (dataProvider)
            {
                case "SqlServer":
                    ida = new SqlDataAdapter((SqlCommand)cmd);
                    break;
                case "OleDb":
                    ida = new OleDbDataAdapter((OleDbCommand)cmd);
                    break;
                case "Odbc":
                    ida = new OdbcDataAdapter((OdbcCommand)cmd);
                    break;
                case "MySql":
                    ida = new MySqlDataAdapter((MySqlCommand)cmd);
                    break;
            }

            return ida;
        }
    }
}